﻿<%@ WebHandler Language="C#" Class="RBStepDispatchReport" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using ReportBusiness;

/**
 * Description: 热表工步派报工报表
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2023.2.8
 **/

public class RBStepDispatchReport : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    string getQuerySql(JObject jObject)
    {
        string factoryid = "", workcenterid = "", teamid = "";
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        if (selectOrg.Count == 3)
        {
            factoryid = selectOrg[0].ToString();
            workcenterid = selectOrg[1].ToString();
            teamid = selectOrg[2].ToString();
        }
        else if (selectOrg.Count == 2)
        {
            factoryid = selectOrg[0].ToString();
            workcenterid = selectOrg[1].ToString();
        }
        else
        {
            factoryid = selectOrg[0].ToString();
        }
        string sql = @"
SELECT f.factoryname,cci.familylistnames,cci.containername,cci.productname,cci.productdescription,
       w.workcentername,t.teamname,s.stepno,s.description stepname,
       csd.createdate dispatchdate,e.fullname dispatchemp,csd.qty dispatchqty,
       csw.createdate reportdate,e1.fullname reportemp,csw.qty reportqty,
       cki.createdate checkdate,e2.fullname checkemp,cki.acceptanceqty,cki.lossqty,
       CASE WHEN cki.createdate IS NOT NULL THEN '已检验'
            WHEN csw.createdate IS NOT NULL THEN '待检验'
            ELSE '待报工' 
       END status,
       wfb.workflowname || ':' || wf.workflowrevision dispatchworkflowinfo,
       cci.workflowname || ':' || cci.workflowrevision currentworkflowinfo
FROM containerstepdispatch2 csd
LEFT JOIN mes_factory_mapping f ON f.factoryid = csd.factoryid
INNER JOIN containercurrentinfo cci ON cci.containerid = csd.containerid
LEFT JOIN Workcenter w ON w.workcenterid = csd.workcenterid
LEFT JOIN team t ON t.teamid = csd.teamid
LEFT JOIN stepinfo s ON s.stepinfoid = csd.stepinfoid
LEFT JOIN containerstepworkreport2 csw ON csw.containerstepdispatchid = csd.containerstepdispatchid AND csw.isuse = 0
LEFT JOIN containercheckinfo2 cki ON cki.containerstepworkreportid = csw.containerstepworkreportid AND cki.isuse = 0
LEFT JOIN employee e ON e.employeeid = csd.employeeid
LEFT JOIN employee e1 ON e1.employeeid = csw.workerid
LEFT JOIN employee e2 ON e2.employeeid = cki.checkerid
LEFT JOIN workflowstep ws ON ws.workflowstepid = csd.workflowstepid
LEFT JOIN workflow wf ON wf.workflowid = ws.workflowid
LEFT JOIN workflowbase wfb ON wfb.workflowbaseid = wf.workflowbaseid
WHERE csd.isuse = 0 ";
        // 分厂
        if (factoryid != "")
        {
            sql += string.Format(" AND csd.factoryid = '{0}'", factoryid);
        }

        // 工区
        if (workcenterid != "")
        {
            sql += string.Format(" AND w.workcenterid = '{0}'", workcenterid);
        }

        // 班组
        if (teamid != "")
        {
            sql += string.Format(" AND t.teamid = '{0}'", teamid);
        }

        // 订单号
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND cci.mfgordername = '{0}'", jObject["mfgordername"].ToString());
        }

        // 机型
        if (jObject["productfamilyname"] != null && jObject["productfamilyname"].ToString() != "")
        {
            sql += string.Format(" AND cci.familylistnames = '{0}'", jObject["productfamilyname"].ToString());
        }

        // 工步号
        if (jObject["stepno"] != null && jObject["stepno"].ToString() != "")
        {
            sql += string.Format(" AND s.stepno = '{0}'", jObject["stepno"].ToString());
        }

        // 零件图号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        // 流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        // 派工人
        if (jObject["dispatcher"] != null && jObject["dispatcher"].ToString() != "")
        {
            sql += string.Format(" AND csd.employeeid = '{0}'", jObject["dispatcher"].ToString());
        }

        // 报工人
        if (jObject["worker"] != null && jObject["worker"].ToString() != "")
        {
            sql += string.Format(" AND csw.workerid = '{0}'", jObject["worker"].ToString());
        }

        // 检验人
        if (jObject["checker"] != null && jObject["checker"].ToString() != "")
        {
            sql += string.Format(" AND cki.checkerid = '{0}'", jObject["checker"].ToString());
        }

        // 派工日期
        if (jObject["dispatchdate"] != null && jObject["dispatchdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["dispatchdate"].ToString());
            sql += string.Format(" AND csd.createdate >= TO_DATE('{0} 00:00:00','YYYY-MM-DD hh24:mi:ss') ", arr[0].ToString());
            sql += string.Format(" AND csd.createdate <= TO_DATE('{0} 23:59:59','YYYY-MM-DD hh24:mi:ss') ", arr[1].ToString());
        }

        // 报工日期
        if (jObject["reportdate"] != null && jObject["reportdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["reportdate"].ToString());
            sql += string.Format(" AND csw.createdate >= TO_DATE('{0} 00:00:00','YYYY-MM-DD hh24:mi:ss') ", arr[0].ToString());
            sql += string.Format(" AND csw.createdate <= TO_DATE('{0} 23:59:59','YYYY-MM-DD hh24:mi:ss') ", arr[1].ToString());
        }

        // 检验日期
        if (jObject["checkdate"] != null && jObject["checkdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["checkdate"].ToString());
            sql += string.Format(" AND cki.createdate >= TO_DATE('{0} 00:00:00','YYYY-MM-DD hh24:mi:ss') ", arr[0].ToString());
            sql += string.Format(" AND cki.createdate <= TO_DATE('{0} 23:59:59','YYYY-MM-DD hh24:mi:ss') ", arr[1].ToString());
        }

        // 状态
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            string status = jObject["status"].ToString();
            if (status == "待报工")
            {
                sql += " AND csw.createdate IS NULL ";
            }
            else if (status == "待检验")
            {
                sql += " AND csw.createdate IS NOT NULL AND cki.createdate IS NULL ";
            }
            else
            {
                sql += " AND cki.createdate IS NOT NULL ";
            }
        }

        return sql;
    }

    //查询方法
    Result getData(JObject jObject)
    {
        string sql = getQuerySql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        string sql = getQuerySql(jObject);
        DataTable dt = dal.GetDataTable(sql);
        dt.Columns["factoryname"].Caption = "分厂";
        dt.Columns["familylistnames"].Caption = "机型";
        dt.Columns["containername"].Caption = "流水卡号";
        dt.Columns["productname"].Caption = "零件图号";
        dt.Columns["productdescription"].Caption = "零件名称";
        dt.Columns["workcentername"].Caption = "工区";
        dt.Columns["teamname"].Caption = "班组";
        dt.Columns["stepno"].Caption = "工步号";
        dt.Columns["stepname"].Caption = "工步";
        dt.Columns["dispatchdate"].Caption = "派工时间";
        dt.Columns["dispatchemp"].Caption = "派工人";
        dt.Columns["dispatchqty"].Caption = "派工数";
        dt.Columns["reportdate"].Caption = "报工时间";
        dt.Columns["reportemp"].Caption = "报工人";
        dt.Columns["reportqty"].Caption = "报工数";
        dt.Columns["checkdate"].Caption = "检验时间";
        dt.Columns["checkemp"].Caption = "检验人";
        dt.Columns["acceptanceqty"].Caption = "合格数";
        dt.Columns["lossqty"].Caption = "报废数";
        dt.Columns["status"].Caption = "状态";
        dt.Columns["dispatchworkflowinfo"].Caption = "派工工艺";
        dt.Columns["currentworkflowinfo"].Caption = "当前工艺";
        ExportHelper.ExportExcel(dt, jObject["factoryname"].ToString() + "工步派报工报表");
    }
}